package com.zhang.common.file;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.UnderlineStyle;

import java.io.IOException;
import java.util.Locale;
import java.awt.Dimension;
import java.io.File;

import java.io.OutputStream;
import jxl.Cell;
import jxl.SheetSettings;

import jxl.biff.FontRecord;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.Font;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * ExcelWriter
 * 使用方式（例）：
 *          er = new ExcelWriter(fileName);
 *             // 创建工作簿
 *             er.createSheet("示例工作簿" + DateUtils.nowTimeString(), 0);
 *             Dimension start;
 *             Dimension end = null;
 *             if(topics != null && topics.length > 0) {
 *                 // 写表头
 *                 // 表头表格开始位置
 *                 start = er.getOffset();
 *                 for (int i = 0; i < topics.length; i++) {
 *                     er.writeCell(topics[i]);
 *                 }
 *                 // 表头表格结束位置
 *                 end = er.getOffset();
 *                 // 设置表头表格样式
 *                 er.setHeader(start, end);
 *                 // 转换到下一行，开始写数据
 *                 er.nextRow();
 *             }
 *             er.close();
 *            ......
 * @author zhangxin
 * @date 2021/6/23
 */
public class ExcelWriter {
    private WritableWorkbook workbook = null;
    private WritableSheet sheet = null;
    private int rowNo = 0;
    private int columnNo = 0;
    private int fontSize = 10;
    private boolean hasWrite = false;

    public ExcelWriter(OutputStream os) throws IOException, WriteException {
        WorkbookSettings ws = new WorkbookSettings();
        ws.setLocale(Locale.getDefault());
        this.workbook = Workbook.createWorkbook(os, ws);
    }

    public ExcelWriter(String excelFile) throws IOException, WriteException {
        WorkbookSettings ws = new WorkbookSettings();
        ws.setLocale(Locale.getDefault());
        this.workbook = Workbook.createWorkbook(new File(excelFile), ws);
    }

    private CellFormat getCellFormat() {
        WritableCellFormat wcf = new WritableCellFormat(WritableWorkbook.NORMAL_STYLE);

        try {
            wcf.setWrap(true);
        } catch (WriteException var3) {
        }

        WritableFont wf = new WritableFont(WritableFont.ARIAL, this.fontSize, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
        wcf.setFont(wf);
        return wcf;
    }

    private WritableCellFormat cloneCellFormat(CellFormat cf) throws WriteException {
        WritableCellFormat wcf = new WritableCellFormat(WritableWorkbook.NORMAL_STYLE);
        Colour bgColor = cf.getBackgroundColour();
        if (!bgColor.equals(Colour.DEFAULT_BACKGROUND) && !bgColor.equals(Colour.DEFAULT_BACKGROUND1)) {
            wcf.setBackground(bgColor);
        }

        Font f = cf.getFont();
        wcf.setFont((FontRecord)f);
        wcf.setAlignment(cf.getAlignment());
        wcf.setVerticalAlignment(cf.getVerticalAlignment());
        Border[] bs = new Border[]{Border.ALL, Border.TOP, Border.RIGHT, Border.BOTTOM, Border.LEFT};
        Border[] var6 = bs;
        int var7 = bs.length;

        for(int var8 = 0; var8 < var7; ++var8) {
            Border b = var6[var8];
            BorderLineStyle bls = cf.getBorderLine(b);
            Colour c = cf.getBorderColour(b);
            wcf.setBorder(b, bls, c);
        }

        return wcf;
    }

    public void createSheet(String strName, int nIndex) {
        this.sheet = this.workbook.createSheet(strName, nIndex);
        SheetSettings ss = this.sheet.getSettings();
        if (ss != null) {
            int i = 0;

            while(i++ < 100) {
                ss.setFitWidth(i);
            }
        }

        this.rowNo = 0;
        this.columnNo = 0;
        this.hasWrite = false;
    }

    public void setSheetIndex(int nIndex) {
        this.sheet = this.workbook.getSheet(nIndex);
        this.rowNo = 0;
        this.columnNo = 0;
        this.hasWrite = false;
    }

    public boolean nextRow() {
        ++this.rowNo;
        this.columnNo = 0;
        this.hasWrite = false;
        return true;
    }

    public void toRow(int nRow) {
        this.rowNo = nRow;
        this.columnNo = 0;
        this.hasWrite = false;
    }

    public void toColumn(int nColumn) {
        this.columnNo = nColumn;
        this.hasWrite = false;
    }

    public Dimension getOffset() {
        return new Dimension(this.hasWrite ? this.columnNo - 1 : this.columnNo, this.rowNo);
    }

    public void merge(Dimension offset1, Dimension offset2) throws RowsExceededException, WriteException {
        this.sheet.mergeCells(offset1.width, offset1.height, offset2.width, offset2.height);
    }

    public void writeCell(Object content) throws RowsExceededException, WriteException {
        if (content == null) {
            content = "";
        }

        Label l = new Label(this.columnNo++, this.rowNo, content.toString(), this.getCellFormat());
        this.sheet.addCell(l);
        this.hasWrite = true;
    }

    public void writeImage(String imagePath, double imageWidth, double imageHeight) {
        File f = new File(imagePath);
        WritableImage img = new WritableImage((double)this.columnNo, (double)this.rowNo, imageWidth, imageHeight, f);
        this.sheet.addImage(img);
    }

    public void setHeader(Dimension offset1, Dimension offset2) throws WriteException {
        int x1 = offset1.width;
        int y1 = offset1.height;
        int x2 = offset2.width;
        int y2 = offset2.height;

        for(int i = x1; i <= x2; ++i) {
            for(int j = y1; j <= y2; ++j) {
                WritableCellFormat cf = (WritableCellFormat)this.getCellFormat();
                cf.setBackground(Colour.OCEAN_BLUE);
                WritableFont wf = (WritableFont)cf.getFont();
                wf.setColour(Colour.WHITE);
                wf.setBoldStyle(WritableFont.BOLD);
                if (j == y1) {
                    cf.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.LIGHT_BLUE);
                } else {
                    cf.setBorder(Border.TOP, BorderLineStyle.DOTTED, Colour.ICE_BLUE);
                }

                if (i == x2) {
                    cf.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.LIGHT_BLUE);
                } else {
                    cf.setBorder(Border.RIGHT, BorderLineStyle.DOTTED, Colour.ICE_BLUE);
                }

                if (j == y2) {
                    cf.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.ICE_BLUE);
                } else {
                    cf.setBorder(Border.BOTTOM, BorderLineStyle.DOTTED, Colour.ICE_BLUE);
                }

                if (i == x1) {
                    cf.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.LIGHT_BLUE);
                } else {
                    cf.setBorder(Border.LEFT, BorderLineStyle.DOTTED, Colour.ICE_BLUE);
                }

                cf.setAlignment(Alignment.CENTRE);
                cf.setVerticalAlignment(VerticalAlignment.CENTRE);
                Cell c = this.sheet.getCell(i, j);
                if (!(c instanceof Label)) {
                    Label l = new Label(i, j, "", cf);
                    this.sheet.addCell(l);
                } else {
                    ((Label)c).setCellFormat(cf);
                }
            }
        }

    }

    public void setBorder(Dimension offset1, Dimension offset2) throws WriteException {
        int x1 = offset1.width;
        int y1 = offset1.height;
        int x2 = offset2.width;
        int y2 = offset2.height;

        for(int i = x1; i <= x2; ++i) {
            for(int j = y1; j <= y2; ++j) {
                CellFormat cf1 = this.sheet.getCell(i, j).getCellFormat();
                WritableCellFormat cf = cf1 != null ? this.cloneCellFormat(cf1) : new WritableCellFormat();
                if (j == y1) {
                    cf.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.LIGHT_BLUE);
                } else {
                    cf.setBorder(Border.TOP, BorderLineStyle.DOTTED, Colour.LIGHT_BLUE);
                }

                if (i == x2) {
                    cf.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.LIGHT_BLUE);
                } else {
                    cf.setBorder(Border.RIGHT, BorderLineStyle.DOTTED, Colour.LIGHT_BLUE);
                }

                if (j == y2) {
                    cf.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.LIGHT_BLUE);
                } else {
                    cf.setBorder(Border.BOTTOM, BorderLineStyle.DOTTED, Colour.LIGHT_BLUE);
                }

                if (i == x1) {
                    cf.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.LIGHT_BLUE);
                } else {
                    cf.setBorder(Border.LEFT, BorderLineStyle.DOTTED, Colour.LIGHT_BLUE);
                }

                Cell c = this.sheet.getCell(i, j);
                if (!(c instanceof Label)) {
                    Label l = new Label(i, j, "", cf);
                    this.sheet.addCell(l);
                } else {
                    ((Label)c).setCellFormat(cf);
                }
            }
        }

    }

    public void setAlignment(Dimension offset1, Dimension offset2, String align) throws WriteException {
        if (align != null) {
            align = align.trim().toLowerCase();
            int x1 = offset1.width;
            int y1 = offset1.height;
            int x2 = offset2.width;
            int y2 = offset2.height;
            Alignment ali = null;
            if ("left".equals(align)) {
                ali = Alignment.LEFT;
            } else if ("center".equals(align)) {
                ali = Alignment.CENTRE;
            } else if ("centre".equals(align)) {
                ali = Alignment.CENTRE;
            } else if ("right".equals(align)) {
                ali = Alignment.RIGHT;
            } else if ("justify".equals(align)) {
                ali = Alignment.JUSTIFY;
            }

            VerticalAlignment val = null;
            if (ali == null) {
                if ("top".equals(align)) {
                    val = VerticalAlignment.TOP;
                } else if ("middle".equals(align)) {
                    val = VerticalAlignment.CENTRE;
                } else if ("bottom".equals(align)) {
                    val = VerticalAlignment.BOTTOM;
                }

                if (val == null) {
                    return;
                }
            }

            for(int i = x1; i <= x2; ++i) {
                for(int j = y1; j <= y2; ++j) {
                    CellFormat cf1 = this.sheet.getCell(i, j).getCellFormat();
                    WritableCellFormat cf = cf1 != null ? this.cloneCellFormat(cf1) : new WritableCellFormat();
                    if (ali != null) {
                        cf.setAlignment(ali);
                    } else {
                        cf.setVerticalAlignment(val);
                    }

                    Cell c = this.sheet.getCell(i, j);
                    if (!(c instanceof Label)) {
                        Label l = new Label(i, j, "", cf);
                        this.sheet.addCell(l);
                    } else {
                        ((Label)c).setCellFormat(cf);
                    }
                }
            }

        }
    }

    public void setColumnWidth(int columnIndex, int width) throws RowsExceededException, WriteException {
        this.sheet.setColumnView(columnIndex, width);
    }

    public void setRowHeight(int rowIndex, int height) throws RowsExceededException, WriteException {
        this.sheet.setRowView(rowIndex, height);
    }

    public void close() throws IOException, WriteException {
        this.workbook.write();
        this.workbook.close();
    }
}

